Transforming and Loading the Data / Data Modeling and Creating new columns, DateDimension table 11


Step 1 - Text file with the new columns


In the zip file that you extracted in the previous Step 9, open the text file, WakeCountyRealEstateProperty_1990-2020_CalculatedColumns_v01.txt, that has the formulas to create columns.  The file is located in the DAX_Measures_Columns folder.



Step 2 -Load Data

With the Power BI Report file open, click on Home ->  Get Data ->  and under Common Data Sources, select Excel.  Browse to the dataset folder and select the Excel files, WakeCounty_Real_Estate_Parcel_Identification_v02.xlsx ->  Select the worksheet and click load. 

Follow the above steps for all the Microsoft Excel, .xlsx, files:


WakeCounty_Real_Estate_Property_1990-2020_v02.xlsx

WakeCounty_Real_Estate_Owner_v02.xlsx

WakeCounty_Real_Estate_PhysicalAddress_v02.xlsx

WakeCounty_Real_Estate_Story_Height_v02.xlsx

WakeCounty_Real_Estate_Township_v02.xlsx

WakeCounty_Real_Estate_Type_and_Use_v02.xlsx


The tables will be loaded and appear on the right-hand side of the window pane.




For the Township spreadsheet, be sure to select the Township worksheet:


And select PhysicalAddress spreadsheet, in the PhysicalAddress worksheet:


Also, select Owner spreadsheet for the Real_Estate_Owner spreadsheet:







Also, select Parcel_Identification spreadsheet, in the Parcel_Identification worksheet.


Also, Property in the Property worksheet and

Once the table is loaded, click on the table,Property, on right-hand side of the window pane.


Also, Story_Height worksheet in the Story_Height spreadsheet.


Note:  

If the loaded table does not have the correct column names, if the first row is not detected as the header, click on the Home ribbon ->  Transform Data -> e.g., select Story_Height .




Select Use First Row as Headers. To save the changes, click on Close & Apply.





Step 3 - Create the new columns that will be needed


Open the text file, found in the folder DAX_Measures_Columns, the file is named WakeCountyRealEstateProperty_1990-2020_CalculatedColumns_v01.txt


After clicking on the table, Property, click to Table Tools ->  New Column and add all the columns in the text file.

The following example is how to create the file column, Size_Range_Residential.



Follow the same steps to create the remainder new columns.


Note:  If you don't see the option to add a new column, be sure you are in the 'Table Tools' tab.  Also, be sure to click on 'New Column' first, to avoid overwriting your previously created column(s).


Verify all columns were created successfully:





Step 4: Create a DateKey column:

In order to set up a Date hierarchy, we need to set up a DateKey column.

Open the Property table, duplicate the Total_Sale_Date Column

Rename the new column DateKey

Click on the new DateKey column and verify hierarchy has been set up.





Step 5: Create a Date Dimension Table:

Open the DateDimensionTable.txt file.  Copy all the lines in the text file.

Still in the Data view, create a new table and paste the code you previously copied.



Sort DateDimension Table


In order for the months to be sorted chronologically rather than alphabetically, click on the Table Tools ->  click on Data View -> 


In the Fields pane, ensure that the MonthAbbr  field is selected (when selected, it will have a dark gray background).

On the Column Tools contextual ribbon, from inside the Sort group, click Sort by Column, and then select MonthNumber.


Do the same for the rest of the columns:

Highlight MonthFull ->  Sort by Column ->  MonthNumber

Highlight DayOfWeekFull ->  Sort by Column ->  Weekday_Number

Highlight WeekDay ->  Sort by Column ->  Weekday_Number

Highlight WeekDayShort ->  Sort by Column ->  Weekday_Number





Step 6:  Create Table_AptPricePerUnit_PerPricePerSqFt table


Create the following SUMAMARIZECOLUMNS table from the text file, SummarizeColumns.txt 



Note: 

You will see warnings stating e.g., 'Column '%measure_name%' in table '%table_name%' cannot be found or may not be used in this expression.  Ignore the messages for now as the errors will go away as measures are been created successfully.  They are dependent on other measures.

If you don't see the option to add a new measure, be sure you are in the 'Table Tools' tab.  Also, be sure to click on 'New Measure' first, to avoid overwriting your previously created measure(s).


When finished, save your Power BI Report file.






To load Excel data in Power BI:

Connect to Excel in Power BI Desktop


To create a blank table:

Creating a Table in Power BI Using DAX Table Constructor